from openpyxl import Workbook, load_workbook

def read_a(file_name):
    workbook = load_workbook(filename=file_name, read_only=True, data_only=True)
    sheet = workbook.active
    max_row = sheet.max_row
    # print(max_row)
    rows_iter = sheet.iter_rows(min_col=1, min_row=2,
                                max_col=1, max_row=max_row)
    a_list = [row[0].value for row in rows_iter]
    print(f'got {len(a_list)} rows from {file_name}')
    return a_list


def read_b(file_name):
    workbook = load_workbook(filename=file_name, read_only=True, data_only=True)
    sheet = workbook.active
    max_row = sheet.max_row
    # print(max_row)
    rows_iter = sheet.iter_rows(min_col=1, min_row=2,
                                max_col=2, max_row=max_row)
    b_12_list = [(row[0].value, row[1].value) for row in rows_iter]
    print(f'got {len(b_12_list)} rows from {file_name}')
    return b_12_list


def find_2_not_in_a(a_list, b_12_list):
    b2_list = [x for x in b_12_list if x[1] == 2]
    missings = list(set([b[0] for b in b2_list]) - set(a_list))
    print(f'b 2 missing {len(missings)} from a')
    return missings


def write_missings(missings, file_name):
    # print(missings, file_name)
    workbook = Workbook()
    sheet = workbook.active
    for i in range(len(missings)):
        sheet.cell(row=i + 1, column=1).value = missings[i]
    workbook.save(filename=file_name)
    print(f'saved to {file_name}')


if __name__ == '__main__':
    a_list = read_a(r'C:\Users\YANG.LEI\Downloads\a.xlsx')
    b_12_list = read_b(r'C:\Users\YANG.LEI\Downloads\b.xlsx')
    missings = find_2_not_in_a(a_list, b_12_list)
    write_missings(missings, r'C:\Users\YANG.LEI\Downloads\c.xlsx')
